﻿
using System.Data;
using System.Text;
using System.Data.Common;
using System.ComponentModel;

using Newtonsoft.Json;
using OpenLink.Data.VirtuosoNET;
using ModelContextProtocol.Server;
using Microsoft.Extensions.Logging;
using ModelContextProtocol.Protocol;
using edan.zhjk.Commons.DBOption;
using Furion;

namespace edan.zhjk.Commons.MCPService.VirtuosoDB
{
    /// <summary>
    /// Virtuoso数据库Mcp工具类
    /// </summary>
    [McpServerToolType, Description("Virtuoso数据库")]
    public sealed class VirtuosoDBTool
    {
        private readonly ILogger<VirtuosoDBTool> _logger;
        private static MutiDBConns? MutiDBConns;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="logger"></param>
        public VirtuosoDBTool(ILogger<VirtuosoDBTool> logger)
        {
            _logger = logger;
            var dbOptions = App.GetOptionsMonitor<DBOptions>();
            MutiDBConns = dbOptions?.MutiDBConns?.FirstOrDefault(it => it.DbType == DataBaseType.Virtuoso);
        }

        /// <summary>
        /// 读取OpenLink Virtuoso连接
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        /// <exception cref="InvalidOperationException"></exception>
        private VirtuosoConnection GetVirtuosoConnection(string? url)
        {
            var finalUrl = string.IsNullOrWhiteSpace(url) ? MutiDBConns?.Connection : url;

            if (string.IsNullOrWhiteSpace(finalUrl))
                throw new InvalidOperationException("ADO_URL is required and was not provided or set in environment.");

            var builder = new VirtuosoConnectionStringBuilder(finalUrl);
            return new VirtuosoConnection(builder.ToString());
        }

        /// <summary>
        /// 查询所有表
        /// </summary>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_get_all_table"), Description("[Virtuoso]获取数据库所有表")]
        public async Task<CallToolResult> GetAllTable([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';";
                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

                var schemas = new List<string>();
                while (await reader.ReadAsync(cancellationToken))
                {
                    schemas.Add(reader.GetString(0));
                }

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(schemas, settings) }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 从已连接的数据库中检索并返回所有模式/目录名称的列表。
        /// </summary>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_get_schemas"), Description("[Virtuoso]获取已连接的数据库中检索并返回所有模式/目录名称的列表。")]
        public async Task<CallToolResult> GetSchemas([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = "select distinct name_part(KEY_TABLE,0) AS TABLE_CAT VARCHAR(128) from DB.DBA.SYS_KEYS order by 1";
                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

                var schemas = new List<string>();
                while (await reader.ReadAsync(cancellationToken))
                {
                    schemas.Add(reader.GetString(0));
                }

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(schemas, settings) }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 检索并返回一个列表，其中包含指定模式中表的信息。
        /// </summary>
        /// <param name="schema">模式名称</param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_get_tables"), Description("[Virtuoso]检索并返回一个列表，其中包含指定模式中表的信息。")]
        public async Task<CallToolResult> GetTables([Description("模式名称")] string? schema = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                var restrictions = new string?[3];
                restrictions[0] = schema ?? null; // Catalog
                restrictions[2] = "%";

                var tableSchema = await conn.GetSchemaAsync("Tables", restrictions, cancellationToken);

                var tables = new List<Dictionary<string, string?>>();
                foreach (DataRow row in tableSchema.Rows)
                {
                    var table = new Dictionary<string, string?>
                    {
                        ["TABLE_CAT"] = row[0].ToString(),
                        ["TABLE_SCHEM"] = row[1].ToString(),
                        ["TABLE_NAME"] = row[2].ToString()
                    };
                    tables.Add(table);
                }

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(tables, settings) }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 检索并返回有关表的完整元数据，包括列、主键和外键。
        /// </summary>
        /// <param name="schema">模式名称</param>
        /// <param name="table">表名</param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_describe_table"), Description("[Virtuoso]检索并返回有关表的完整元数据，包括列、主键和外键。")]
        public async Task<CallToolResult> DescribeTable([Description("模式名称")] string? schema = null
            , [Description("表名")] string table = ""
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                var tableInfo = await GetTableInfoAsync(conn, schema, table, cancellationToken);

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(tableInfo, settings) }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 列出名称中包含给定子字符串的表
        /// </summary>
        /// <param name="q">sql字符串</param>
        /// <param name="schema">模式名称</param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        [McpServerTool(Name = "virtuosodb_filter_table_names"), Description("[Virtuoso]列出名称中包含给定子字符串的表。")]
        public async Task<CallToolResult> FilterTableNames([Description("Substring to search")] string q
            , [Description("模式名称")] string? schema = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(q))
                    throw new ArgumentException("Query string cannot be null or empty.", nameof(q));

                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                var restrictions = new string?[3];
                restrictions[0] = schema;
                restrictions[2] = "%";
                var dt = await conn.GetSchemaAsync("Tables", restrictions, cancellationToken);

                var list = new List<Dictionary<string, string?>>();
                foreach (DataRow row in dt.Rows)
                {
                    var name = row[2]?.ToString() ?? string.Empty;
                    if (name.Contains(q, StringComparison.OrdinalIgnoreCase))
                    {
                        list.Add(new Dictionary<string, string?>
                        {
                            ["TABLE_CAT"] = row[0].ToString(),
                            ["TABLE_SCHEM"] = row[1].ToString(),
                            ["TABLE_NAME"] = row[2].ToString()
                        });
                    }
                }

                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = JsonConvert.SerializeObject(list, settings) }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 执行一个 SQL 查询，并以 JSONL 格式返回结果。
        /// </summary>
        /// <param name="query">SQL查询</param>
        /// <param name="max_rows">最大数据条数</param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_execute_query"), Description("[Virtuoso]执行一个 SQL 查询，并以 JSONL 格式返回结果。")]
        public async Task<CallToolResult> ExecuteQuery([Description("SQL查询条件")] string query
            , [Description("最大数据条数")] int? max_rows = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                var maxRows = max_rows ?? 100;
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = query;
                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

                var list = new List<Dictionary<string, object?>>();
                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };

                int count = 0;
                while (await reader.ReadAsync(cancellationToken) && count < maxRows)
                {
                    var row = new Dictionary<string, object?>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var val = reader.GetValue(i);
                        var str = val?.ToString() ?? null;
                        if (str != null && str.Length > MutiDBConns?.MaxLongData)
                            str = str.Substring(0, MutiDBConns?.MaxLongData ?? 100);
                        row[reader.GetName(i)] = str;
                    }
                    list.Add(row);
                    count++;
                }
                var text = JsonConvert.SerializeObject(list, settings);
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 执行一个 SQL 查询，并以 Markdown 表格格式返回结果。
        /// </summary>
        /// <param name="query">SQL查询</param>
        /// <param name="max_rows">最大数据条数</param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_execute_query_md"), Description("[Virtuoso]执行一个 SQL 查询，并以 Markdown 表格格式返回结果。")]
        public async Task<CallToolResult> ExecuteQueryMd([Description("SQL查询条件")] string query
            , [Description("最大数据条数")] int? max_rows = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                var maxRows = max_rows ?? 100;
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = query;
                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

                var colNames = Enumerable.Range(0, reader.FieldCount)
                    .Select(i => reader.GetName(i))
                    .ToList();

                var md = new StringBuilder();
                md.Append("| ").Append(string.Join(" | ", colNames)).AppendLine(" |");
                md.Append("| ").Append(string.Join(" | ", colNames.Select(n => "---"))).AppendLine(" |");

                int count = 0;
                while (await reader.ReadAsync(cancellationToken) && count < maxRows)
                {
                    md.Append("| ");
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var val = reader.GetValue(i)?.ToString() ?? string.Empty;
                        if (val.Length > MutiDBConns?.MaxLongData)
                            val = val.Substring(0, MutiDBConns?.MaxLongData ?? 100);
                        md.Append(val).Append(" | ");
                    }
                    md.AppendLine();
                    count++;
                }
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = md.ToString() }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 执行一个 SQL 查询，并以 JSONL 格式返回结果。
        /// </summary>
        /// <param name="query"></param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        [McpServerTool(Name = "virtuosodb_query_database"), Description("[Virtuoso]执行一个 SQL 查询，并以 JSONL 格式返回结果。")]
        public async Task<CallToolResult> QueryDatabase([Description("SQL查询条件")] string query
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(query))
                    throw new ArgumentException("Query string cannot be null or empty.", nameof(query));
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = query;
                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);

                var list = new List<Dictionary<string, object?>>();
                var settings = new JsonSerializerSettings
                {
                    Formatting = Formatting.None,
                    NullValueHandling = NullValueHandling.Include
                };

                while (await reader.ReadAsync(cancellationToken))
                {
                    var row = new Dictionary<string, object?>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var val = reader.GetValue(i);
                        var str = val?.ToString() ?? null;
                        if (str != null && str.Length > MutiDBConns?.MaxLongData)
                            str = str.Substring(0, MutiDBConns?.MaxLongData ?? 100);
                        row[reader.GetName(i)] = str;
                    }
                    list.Add(row);
                }
                var text = JsonConvert.SerializeObject(list, settings);
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 执行一个 SPASQL 查询并返回结果。
        /// </summary>
        /// <param name="query"></param>
        /// <param name="max_rows"></param>
        /// <param name="timeout"></param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        [McpServerTool(Name = "virtuosodb_spasql_query"), Description("[Virtuoso]执行一个 SPASQL 查询并返回结果。")]
        public async Task<CallToolResult> SpasqlQuery([Description("SPASQL query")] string query
            , [Description("Max Rows")] int? max_rows = null
            , [Description("Timeout ms")] int? timeout = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(query))
                    throw new ArgumentException("Query string cannot be null or empty.", nameof(query));
                var maxRows = max_rows ?? 20;
                var timeoutValue = timeout ?? 300000;

                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);
                await using var cmd = conn.CreateCommand();

                cmd.CommandText = $"select Demo.demo.execute_spasql_query(?, ?, ?) as result";
                cmd.Parameters.Add(new VirtuosoParameter { ParameterName = "@query", Value = query, DbType = DbType.AnsiString });
                cmd.Parameters.Add(new VirtuosoParameter("@maxrows", maxRows));
                cmd.Parameters.Add(new VirtuosoParameter("@timeout", timeoutValue));

                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
                var text = (await reader.ReadAsync(cancellationToken)) ? reader.GetString(0) : string.Empty;

                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 这是供人工智能测试数据库连接的工具
        /// </summary>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_health_check"), Description("[Virtuoso]检查数据库连接是否正常且可用")]
        public async Task<CallToolResult> HealthCheck([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = "true" }]
                };
            }
            catch (Exception e)
            {
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = "false" }]
                };
            }
        }

        /// <summary>
        /// 与专业支持智能机器人进行互动
        /// </summary>
        /// <param name="prompt"></param>
        /// <param name="api_key"></param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_virtuoso_support_ai"), Description("[Virtuoso]与专业支持智能机器人进行互动。")]
        public async Task<CallToolResult> VirtuosoSupportAi([Description("Prompt for AI agent")] string prompt
            , [Description("API Key")] string? api_key = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                var key = string.IsNullOrWhiteSpace(api_key) ? MutiDBConns?.ApiKey : api_key;

                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = "select DEMO.DBA.OAI_VIRTUOSO_SUPPORT_AI(?, ?) as result";
                cmd.Parameters.Add(new VirtuosoParameter("@prompt", prompt));
                cmd.Parameters.Add(new VirtuosoParameter("@key", key));

                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
                var text = (await reader.ReadAsync(cancellationToken)) ? reader.GetString(0) : string.Empty;
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 使用 SPARQL 人工智能代理功能。
        /// </summary>
        /// <param name="prompt"></param>
        /// <param name="api_key"></param>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_sparql_func"), Description("[Virtuoso]使用 SPARQL 人工智能代理功能。")]
        public async Task<CallToolResult> SparqlFunc([Description("Prompt for AI function")] string prompt
            , [Description("API Key")] string? api_key = null
            , [Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            try
            {
                var key = string.IsNullOrWhiteSpace(api_key) ? MutiDBConns?.ApiKey : api_key;

                await using var conn = GetVirtuosoConnection(url);
                await conn.OpenAsync(cancellationToken);

                await using var cmd = conn.CreateCommand();
                cmd.CommandText = "select DEMO.DBA.OAI_SPARQL_FUNC(?, ?) as result";
                cmd.Parameters.Add(new VirtuosoParameter("@prompt", prompt));
                cmd.Parameters.Add(new VirtuosoParameter("@key", key));

                await using var reader = await cmd.ExecuteReaderAsync(cancellationToken);
                var text = (await reader.ReadAsync(cancellationToken)) ? reader.GetString(0) : string.Empty;
                return new CallToolResult
                {
                    IsError = false,
                    Content = [new TextContentBlock() { Type = "text", Text = text }]
                };
            }
            catch (Exception ex) when (ex is not OperationCanceledException)
            {
                return new CallToolResult
                {
                    IsError = true,
                    Content = [new TextContentBlock() { Type = "text", Text = ex.Message }]
                };
            }
        }

        /// <summary>
        /// 此查询会获取 RDF 图中的所有实体类型，以及它们的标签和注释（如果有的话）。
        /// 它会过滤掉空白节点，并确保仅返回 IRI 类型。
        /// LIMIT 子句设置为 100 以限制返回的实体类型数量。
        /// </summary>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_sparql_get_entity_types"), Description(@"[Virtuoso]此查询会获取 RDF 图中的所有实体类型，以及它们的标签和注释（如果有的话）。"
                      + "它会过滤掉空白节点，并确保仅返回 IRI 类型。"
                      + "LIMIT 子句设置为 100 以限制返回的实体类型数量。")]
        public async Task<CallToolResult> SparqlGetEntityTypes([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            var query = @"SELECT DISTINCT * FROM (
  SPARQL 
  PREFIX owl: <http://www.w3.org/2002/07/owl#>
  PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
  SELECT ?o 
  WHERE {
    GRAPH ?g {
        ?s a ?o .
        
        OPTIONAL {
            ?s rdfs:label ?label . 
            FILTER (LANG(?label) = ""en"" || LANG(?label) = """")
        }
        
        OPTIONAL {
            ?s rdfs:comment ?comment . 
            FILTER (LANG(?comment) = ""en"" || LANG(?comment) = """")
        }
        
        FILTER (isIRI(?o) && !isBlank(?o))
    }
  }
  LIMIT 100
) AS x";
            return await QueryDatabase(query, url, cancellationToken);
        }

        /// <summary>
        /// 此查询会获取 RDF 图中的所有实体类型，以及它们的标签和注释（如果有的话）。
        /// 它会过滤掉空白节点，并确保仅返回 IRI 类型。
        /// LIMIT 子句设置为 100 以限制返回的实体类型数量。
        /// </summary>
        /// <param name="url"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_sparql_get_entity_types_detailed"), Description(@"[Virtuoso]此查询会获取 RDF 图中的所有实体类型，以及它们的标签和注释（如果有的话）。"
                     + "它会过滤掉空白节点，并确保仅返回 IRI 类型。 "
                     + "LIMIT 子句设置为 100 以限制返回的实体类型数量。")]
        public async Task<CallToolResult> SparqlGetEntityTypesDetailed([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            var query = @"SELECT * FROM (
        SPARQL
        PREFIX owl: <http://www.w3.org/2002/07/owl#>
        PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
        PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 

        SELECT ?o, (SAMPLE(?label) AS ?label), (SAMPLE(?comment) AS ?comment)
        WHERE {
            GRAPH ?g {
                ?s a ?o .
                OPTIONAL {?o rdfs:label ?label . FILTER (LANG(?label) = ""en"" || LANG(?label) = """")}
                OPTIONAL {?o rdfs:comment ?comment . FILTER (LANG(?comment) = ""en"" || LANG(?comment) = """")}
                FILTER (isIRI(?o) && !isBlank(?o))
            }
        }
        GROUP BY ?o
        ORDER BY ?o
        LIMIT 20
    ) AS results ";
            return await QueryDatabase(query, url, cancellationToken);
        }

        /// <summary>
        /// 此查询会获取 RDF 图中每种类型的实体样本，以及它们的标签和数量。
        /// 它按实体类型进行分组，并按样本数量降序排列结果。
        /// 注意：LIMIT 子句设置为 20 以限制返回的实体类型数量。
        /// </summary>
        /// <param name="url"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_sparql_get_entity_types_samples"), Description(@"[Virtuoso]此查询会获取 RDF 图中每种类型的实体样本，以及它们的标签和数量。"
                    + "它按实体类型进行分组，并按样本数量降序排列结果。"
                    + "注意：LIMIT 子句设置为 20 以限制返回的实体类型数量。")]
        public async Task<CallToolResult> SparqlGetEntityTypesSamples([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            var query = @"SELECT * FROM (
        SPARQL
        PREFIX owl: <http://www.w3.org/2002/07/owl#>
        PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
        PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> 
        SELECT (SAMPLE(?s) AS ?sample), ?slabel, (COUNT(*) AS ?sampleCount), (?o AS ?entityType), ?olabel
        WHERE {
            GRAPH ?g {
                ?s a ?o .
                OPTIONAL {?s rdfs:label ?slabel . FILTER (LANG(?slabel) = ""en"" || LANG(?slabel) = """")}
                FILTER (isIRI(?s) && !isBlank(?s))
                OPTIONAL {?o rdfs:label ?olabel . FILTER (LANG(?olabel) = ""en"" || LANG(?olabel) = """")}
                FILTER (isIRI(?o) && !isBlank(?o))
            }
        }
        GROUP BY ?slabel ?o ?olabel
        ORDER BY DESC(?sampleCount) ?o ?slabel ?olabel
        LIMIT 20
    ) AS results";
            return await QueryDatabase(query, url, cancellationToken);
        }

        /// <summary>
        /// 此查询会获取 RDF 图表中的所有本体，以及它们的标签和注释（如果有的话）。
        /// </summary>
        /// <param name="url">数据库链接字符串</param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        [McpServerTool(Name = "virtuosodb_sparql_get_ontologies"), Description("[Virtuoso]此查询会获取 RDF 图表中的所有本体，以及它们的标签和注释（如果有的话）。")]
        public async Task<CallToolResult> SparqlGetOntologies([Description("数据库链接字符串")] string? url = null
            , CancellationToken cancellationToken = default)
        {
            var query = @"SELECT * FROM (
        SPARQL 
        PREFIX owl: <http://www.w3.org/2002/07/owl#>
        PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
        PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
        SELECT ?s, ?label, ?comment 
        WHERE {
            GRAPH ?g {
                ?s a owl:Ontology .
            
                OPTIONAL {
                    ?s rdfs:label ?label . 
                    FILTER (LANG(?label) = ""en"" || LANG(?label) = """")
                }
            
                OPTIONAL {
                    ?s rdfs:comment ?comment . 
                    FILTER (LANG(?comment) = ""en"" || LANG(?comment) = """")
                }
            
                FILTER (isIRI(?o) && !isBlank(?o))
            }
        }
        LIMIT 100
    ) AS x";
            return await QueryDatabase(query, url, cancellationToken);
        }

        /// <summary>
        /// 返回有关表的完整元数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="schema"></param>
        /// <param name="table"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        private async Task<Dictionary<string, object?>> GetTableInfoAsync(DbConnection conn, string? schema, string table, CancellationToken cancellationToken = default)
        {
            var columns = new List<Dictionary<string, object?>>();
            var primaryKeys = new List<string>();
            var foreignKeys = new List<Dictionary<string, object?>>();

            var tableSchema = await conn.GetSchemaAsync("Tables", [schema ?? null, null, table], cancellationToken);
            if (tableSchema.Rows.Count == 0)
            {
                return new Dictionary<string, object?>
                {
                    ["TABLE_CAT"] = conn.Database,
                    ["TABLE_SCHEM"] = schema,
                    ["TABLE_NAME"] = table,
                    ["columns"] = columns,
                    //["primary_keys"] = primaryKeys,
                    //["foreign_keys"] = foreignKeys
                };
            }

            var trow = tableSchema.Rows[0];
            var cat = trow[0].ToString();
            var sch = trow[1].ToString();

            var cols = await conn.GetSchemaAsync("Columns", [cat, sch, table, null], cancellationToken);
            foreach (DataRow row in cols.Rows)
            {
                columns.Add(new Dictionary<string, object?>
                {
                    ["name"] = row[3].ToString(), //"COLUMN_NAME"
                    ["type"] = row[5].ToString(), //"DATA_TYPE"
                    ["column_size"] = row[6], //"CHARACTER_MAXIMUM_LENGTH"
                    ["num_prec_radix"] = row[9], //"NUMERIC_PRECISION_RADIX"
                    ["nullable"] = Convert.ToInt32(row[10]) != 1,
                    ["default"] = row[12] //"COLUMN_DEFAULT"
                });
            }

            var pk = await conn.GetSchemaAsync("PRIMARYKEYS", [cat, sch, table], cancellationToken);
            foreach (DataRow row in pk.Rows)
            {
                primaryKeys.Add(row[3].ToString() ?? ""); //"COLUMN_NAME"
            }

            var fk = await conn.GetSchemaAsync("ForeignKeys", [cat, sch, table], cancellationToken);
            foreach (DataRow row in fk.Rows)
            {
                foreignKeys.Add(new Dictionary<string, object?>
                {
                    ["name"] = row[11].ToString(), //"FK_NAME"
                    ["constrained_columns"] = new List<string> { row[7].ToString()! }, //"FKCOLUMN_NAME"
                    ["referred_cat"] = row[0].ToString(),    //"PKTABLE_CAT"
                    ["referred_schem"] = row[1].ToString(), //"PKTABLE_SCHEM"
                    ["referred_table"] = row[2].ToString(), //"PKTABLE_NAME"
                    ["referred_columns"] = new List<string> { row[3].ToString()! } //"PKCOLUMN_NAME"
                });
            }

            foreach (var column in columns)
            {
                column["primary_key"] = primaryKeys.Contains(column["name"]?.ToString()!);
            }

            return new Dictionary<string, object?>
            {
                ["TABLE_CAT"] = conn.Database,
                ["TABLE_SCHEM"] = schema,
                ["TABLE_NAME"] = table,
                ["columns"] = columns,
                ["primary_keys"] = primaryKeys,
                ["foreign_keys"] = foreignKeys
            };
        }
    }
}
